use OlympusSoftware

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'dbo.AddOpportunity') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop PROCEDURE [dbo].[AddOpportunity]
GO

CREATE PROCEDURE [dbo].[AddOpportunity]
	@title varchar(128),
	@titleEn varchar(128),
	@description nvarchar(2048),
	@descriptionEn nvarchar(2048) 
as
begin

declare @newIdInfo int

select @newIdInfo = (select top 1 IdInfo from [dbo].[GlobalizeInfoLanguages] order by IdInfo desc)
select @newIdInfo = ISNULL(@newIdInfo, -1)
select @newIdInfo = @newIdInfo+1
 
insert into dbo.GlobalizeInfoLanguages(IdInfo, FK_IdLanguage, FK_IdPage, FK_IdSite, TextInfo) values (@newIdInfo, 1, 2, 1, @titleEn)
insert into dbo.GlobalizeInfoLanguages(IdInfo, FK_IdLanguage, FK_IdPage, FK_IdSite, TextInfo) values (@newIdInfo, 2, 2, 1, @title)

select @newIdInfo = (select top 1 IdInfo from [dbo].[GlobalizeInfoLanguages] order by IdInfo desc)
select @newIdInfo = ISNULL(@newIdInfo, -1)
select @newIdInfo = @newIdInfo+1

insert into dbo.GlobalizeInfoLanguages(IdInfo, FK_IdLanguage, FK_IdPage, FK_IdSite, TextInfo) values (@newIdInfo, 1, 2, 1, @descriptionEn)
insert into dbo.GlobalizeInfoLanguages(IdInfo, FK_IdLanguage, FK_IdPage, FK_IdSite, TextInfo) values (@newIdInfo, 2, 2, 1, @description)

declare @newOpportunityId int
select @newOpportunityId = (select top 1 OpportunityId from [dbo].[Opportunity] order by OpportunityId desc)
select @newOpportunityId = ISNULL(@newOpportunityId, -1)
select @newOpportunityId = @newOpportunityId+1

insert into Opportunity([OpportunityId], [TitleInfoId], [DescriptionInfoId]) values (@newOpportunityId, @newIdInfo-1, @newIdInfo)

select @newOpportunityId;
end
GO

GRANT EXECUTE on [dbo].[AddOpportunity] to Zeus
GO

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'dbo.AddOpportunityImage') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop PROCEDURE [dbo].[AddOpportunityImage]
GO

CREATE PROCEDURE [dbo].[AddOpportunityImage]
	@opportunityId varchar(128),
	@name nvarchar(128),
	@contents image 
as
begin

declare @newOpportunityImageId int
select @newOpportunityImageId = (select top 1 OpportunityImageId from [dbo].[OpportunityImage] order by OpportunityImageId desc)
select @newOpportunityImageId = ISNULL(@newOpportunityImageId, -1)
select @newOpportunityImageId = @newOpportunityImageId+1

insert into [OpportunityImage]([OpportunityImageId], [Title], [Contents]) values (@newOpportunityImageId, @name, @contents)

insert into [OpportunityVersusImages]([IdOpportunity], [IdImage]) values(@opportunityId, @newOpportunityImageId)

select @newOpportunityImageId

end
GO

GRANT EXECUTE on [dbo].[AddOpportunityImage] to Zeus



IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'dbo.GetOpportunities') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop PROCEDURE [dbo].[GetOpportunities]
GO

CREATE PROCEDURE [dbo].[GetOpportunities]
as
begin

select o.OpportunityId, o.TitleInfoId, oi.OpportunityImageId  from dbo.Opportunity o inner join dbo.OpportunityVersusImages ovi on o.OpportunityId = ovi.IdOpportunity inner join dbo.OpportunityImage oi on oi.OpportunityImageId = ovi.IdImage

end
GO

GRANT EXECUTE on [dbo].[GetOpportunities] to Zeus

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'dbo.GetOpportunity') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop PROCEDURE [dbo].[GetOpportunity]
GO

CREATE PROCEDURE [dbo].[GetOpportunity]
	@oppId int
as
begin

select * from dbo.Opportunity o left outer join dbo.OpportunityVersusImages ovi on o.OpportunityId = ovi.IdOpportunity left outer join dbo.OpportunityImage oi on oi.OpportunityImageId = ovi.IdImage
where o.OpportunityId = @oppId

end
GO

GRANT EXECUTE on [dbo].[GetOpportunity] to Zeus


IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'dbo.GetOpportunityImage') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop PROCEDURE [dbo].[GetOpportunityImage]
GO
CREATE PROCEDURE [dbo].[GetOpportunityImage]
	@imageId int
as
begin

select [Contents] from [OpportunityImage] where [OpportunityImageId] = @imageId

end
GO

GRANT EXECUTE on [dbo].[GetOpportunityImage] to Zeus


------------------

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'dbo.EditOpportunity') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop PROCEDURE [dbo].[EditOpportunity]
GO

CREATE PROCEDURE [dbo].[EditOpportunity]
@oppId int,
@title varchar(128),
@titleInfoId int,
@titleEn varchar(128),
@titleInfoEnId int,
@description nvarchar(2048),
@descriptionInfoId int,
@descriptionEn nvarchar(2048),
@descriptionEnId int
as
begin

update GlobalizeInfoLanguages
set TextInfo = @title
where IdInfo = @titleInfoId and FK_IdLanguage = 2

update GlobalizeInfoLanguages
set TextInfo = @titleEn
where IdInfo = @titleInfoEnId and FK_IdLanguage = 1

update GlobalizeInfoLanguages
set TextInfo = @description
where IdInfo = @descriptionInfoId and FK_IdLanguage = 2

update GlobalizeInfoLanguages
set TextInfo = @descriptionEn
where IdInfo = @descriptionEnId and FK_IdLanguage = 1

declare @tempIdImages table (id int)

insert into @tempIdImages
select IdImage from dbo.OpportunityVersusImages where IdOpportunity = @oppId

delete from dbo.OpportunityVersusImages where IdOpportunity = @oppId

delete from dbo.OpportunityImage where OpportunityImageId in (select * from @tempIdImages)

end
GO

GRANT EXECUTE on [dbo].[EditOpportunity] to Zeus
GO

-----------------------

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'dbo.EditOpportunityImage') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop PROCEDURE [dbo].[EditOpportunityImage]
GO

CREATE PROCEDURE [dbo].[EditOpportunityImage]
@id int,
@name nvarchar(128),
@contents image
as
begin

update OpportunityImage
set Title = @name, Contents = @contents
where OpportunityImageId = @id

end
GO

GRANT EXECUTE on [dbo].[EditOpportunityImage] to Zeus
GO

-----------------------

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'dbo.RemoveOpportunityImages') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop PROCEDURE [dbo].[RemoveOpportunityImages]
GO

CREATE PROCEDURE [dbo].[RemoveOpportunityImages]
@oppId int
as
begin

declare @tempIdImages table (id int)

insert into @tempIdImages
select IdImage from dbo.OpportunityVersusImages where IdOpportunity = @oppId

delete from dbo.OpportunityVersusImages where IdOpportunity = @oppId

delete from dbo.OpportunityImage where OpportunityImageId in (select * from @tempIdImages)

end
GO

GRANT EXECUTE on [dbo].[RemoveOpportunityImages] to Zeus
GO